package com.note.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;

import com.note.bean.Note;

public class NoteDao extends DerbyDB {
	public int upDate(Note note){
		try {
		final String sql="update note set notedate =  ?,stringdate = ?,feel = ?,body = ?, dbwork = ?,"
				+ "xinde = ?,daiban = ?,zhichujine = ?,zhichumiaoshu = ? where id = ?";
		PreparedStatement prepareStatement=connection.prepareStatement(sql);
		prepareStatement.setTimestamp(1,new Timestamp(note.getDate().getTime()));
		prepareStatement.setString(2, note.getStringdate());
		prepareStatement.setString(3, note.getFeel());
		prepareStatement.setString(4, note.getBody());
		prepareStatement.setString(5, note.getWork());
		prepareStatement.setString(6, note.getXinde());
		prepareStatement.setString(7, note.getDaiban());
		prepareStatement.setString(8, note.getZhichujine());
		prepareStatement.setString(9, note.getZhichumiaoshu());
		System.out.println("note.getZhichujine()"+note.getZhichujine());
		prepareStatement.setInt(10, note.getId());
			int result= prepareStatement.executeUpdate();
			connection.commit();
			return result;
		} catch (SQLException e) { 
			e.printStackTrace();
			return 0;
		}
	}
	public int saveNote(Note note){
		try {
		final String sql="insert into note (notedate,stringdate,feel,body,dbwork,xinde,daiban,zhichujine,zhichumiaoshu) values"
				+ " (?,?,?,?,?,?,?,?,?)";
		PreparedStatement prepareStatement=connection.prepareStatement(sql);
		prepareStatement.setTimestamp(1,new Timestamp(note.getDate().getTime()));
		prepareStatement.setString(2, note.getStringdate());
		prepareStatement.setString(3, note.getFeel());
		prepareStatement.setString(4, note.getBody());
		prepareStatement.setString(5, note.getWork());
		prepareStatement.setString(6, note.getXinde());
		prepareStatement.setString(7, note.getDaiban());
		prepareStatement.setString(8, note.getZhichujine());
		prepareStatement.setString(9, note.getZhichumiaoshu());
			int result= prepareStatement.executeUpdate();
			connection.commit();
			return result;
		} catch (SQLException e) { 
			e.printStackTrace();
			return 0;
		}
	}
	public Note getNoteByDate(Date date){
		try {
			Calendar cld=Calendar.getInstance();
			cld.setTime(date);
			int qyear=cld.get(Calendar.YEAR);
			int qmonth=cld.get(Calendar.MONTH)+1;
			int qdate=cld.get(Calendar.DATE);
			System.out.println(qdate);
		final String sql="select *  from note where YEAR(notedate)=? and MONTH(notedate) = ? and DAY(notedate)=?";
		PreparedStatement prepareStatement=connection.prepareStatement(sql); 
		//System.out.println(qyear+"|"+qmonth+"|"+qdate);
		prepareStatement.setInt(1, qyear);
		prepareStatement.setInt(2, qmonth);
		prepareStatement.setInt(3, qdate);
		ResultSet rs= prepareStatement.executeQuery();
		//System.out.println("res="+rs);
		Note note=new Note();
		while (rs.next()) {
			System.out.println("id="+rs.getString("id"));
			note.setId(rs.getInt("id"));
			note.setDate(rs.getDate("notedate"));
			note.setFeel(rs.getString("feel"));
			note.setStringdate(rs.getString("stringdate"));
			note.setWork(rs.getString("dbwork"));
			note.setXinde(rs.getString("xinde"));
			note.setDaiban(rs.getString("daiban"));
			note.setZhichujine(rs.getString("zhichujine"));
			note.setZhichumiaoshu(rs.getString("zhichumiaoshu"));
			note.setBody(rs.getString("body"));
			
			return note;
		}
		return null;
		} catch (SQLException e) { 
			e.printStackTrace();
			creatTable_Note();
			return null;
		}
	}
	
	
	
	public static void main(String[] args) {
		NoteDao noteDao=new NoteDao();
		Note note=new Note();
		note.setDate(new Date());
		int a=noteDao.saveNote(note);
		int b=noteDao.saveNote(note);
		System.out.println(a+"|"+b);
		
		Note n=noteDao.getNoteByDate(new Date());
		System.out.println(n);
	}
}
